განმეორებადი მონაცემების დაჯამება

განვიხილოთ ახლა კიდევ ერთი სავარჯიშო, რომელშიც გამოყენებული იქნება გაუმჯობესებული ფილტრი და მონაცემთა შერწყმის ბრძანება.

გარდა ამისა, პრაქტიკული ამოცანის საფუძველზე შევისწავლით ორ ახალ საკითხს: შუალედური შედეგის გამოთვლა (Data პანელის Subtotal ბრძანება) და საყრდენი ცხრილის შექმნა (Insert პანელის PivotTable ინსტრუმენტი).

საქონლის რეალიზაცია

საქონლის რეალიზაციის მომენტში ექსელის დავთარში იწერება ორი რიცხვი: საქონლის კოდი (A სვეტში) და რაოდენობა (B სვეტში). დღის განმავლობაში დაგროვდა 50 ასეთი ჩანაწერი. საჭიროა ცხრილი დალაგდეს კოდების მიხედვით და დაითვალოს რეალიზებული რაოდენობები.

საწყისი ცხრილი

გადმოცემის გასამარტივებლად ჩავთვალოთ, რომ იყიდება სულ ხუთი ტიპის საქონელი, რომელთა კოდებია 1001, 1002, 1003, 1004, 1005.

A1 უჯრედში ჩავწეროთ სიტყვა „კოდი“, ხოლო B1–ში – სიტყვა „რაოდენობა“. მეორე სტრიქონიდან დაწყებული, A2:A51 და B2:B51 დიაპაზონებში მიყოლებით შევიტანოთ კოდისა და რაოდენობის შესაბამისი მონაცემები. ეს იქნება საწყისი ცხრილი ამოცანის სხვადასხვა ხერხით ამოსახსნელად.

პირობითი ოპერატორი და ფორმულის “გაყინვა”

გავაკეთოთ საწყისი ცხრილის ასლი ახალ ფურცელზე. ამ პარაგრაფში ასლზე ვიმუშავებთ, ორიგინალი კი შემდგომში გამოდგება სხვა ხერხის ასახსნელად.

ამოცანის ამოსახსნელად გამოვიყენოთ პირობითი ოპერატორი:

1.	დავალაგოთ A სვეტის მონაცემები ანბანის მიხედვით. ეს, რა თქმა უნდა, გააადვილებს ცხრილის აღქმას, თუმცა ცხრილი თითოეული კოდის ჯამურ მონაცემს საერთოდ არ შეიცავს;

2.	C1-ში ჩავწეროთ „მიმდინარე ჯამი“, ხოლო D1–ში – „კოდის შეცვლა“;

3.	C2 უჯრედში ჩავწეროთ ფორმულა: =IF(A2=A1, C1+B2, B2). გავავრცელოთ ეს ფორმულა C3:C51 უჯრედებზე. C სვეტში მივიღებთ ჯამებს.

ახლა A სვეტის ჩამონათვალში უნდა მოიძებნოს თითოეული კოდის ბოლო მნიშვნელობა და იგი გვიჩვენებს იმ დღეს რეალიზებულ რაოდენობას. ესეც საკმაოდ მოუხერხებელია, მითუმეტეს, რომ რეალურად ცხრილი გაცილებით დიდი შეიძლება იყოს. მდგომარეობის გასამარტივებლად ასე მოვიქცეთ:

თუ საქონლის კოდი მომდევნო სტრიქონში იცვლება, D სვეტის უჯრედში ჩავწეროთ სიტყვა „ბოლოა!“, წინააღმდეგ შემთხვევაში - „ჯერ არა“. ამისათვის D2 უჯრედში შევიტანოთ ფორმულა: =IF(A2=A3, "ჯერ არა", "ბოლოა!") და გავავრცელოთ ეს ფორმულა შევსების მარკერით D3:D51 უჯრედებზე.

ახლა უნდა დავალაგოთ მონაცემები D სვეტის მიხედვით, თუმცა C და D სვეტის უჯრედები ფორმულაში სხვა სტრიქონის მისამართებს შეიცავენ და ასეთი მოქმედება, როგორც ზემოთაც იყო აღნიშნული, სახიფათოა. დალაგების შემდეგ C და D სვეტების ზოგიერთი უჯრედი მნიშვნელობას შეიცვლის.

გამოსავალი საკმაოდ მარტივია. საქმე იმაშია, რომ ფორმულები საერთოდ აღარ გვჭირდება და ისინი შეიძლება თავიდან მოვიშოროთ. ამისათვის გამოვიყენოთ ფორმულის „გაყინვის“ ზემოთ ნასწავლი ხერხი:

1.	მოვნიშნოთ C2:D51 დიაპაზონი და შევასრულოთ Copy ბრძანება;

2.	შევასრულოთ Home პანელის Paste / Values ბრძანება;

3.	დავაჭიროთ Esc კლავიშს;

4.	დავდგეთ D სვეტის რომელიმე მონაცემზე და დავალაგოთ ანბანის მიხედვით. ამოცანა შესრულებულია.

ნახატზე მეორე-მეექვსე სტრიქონებში ჩანს ხუთივე კოდის დასახელება (A სვეტში) და მათი დღიური რეალიზაციის რაოდენობა (C სვეტში).

გაუმჯობესებული ფილტრი და ბაზური ფუნქცია

იგივე პრობლემა გადავჭრათ გაუმჯობესებული ფილტრის გამოყენებით. მოცემული გვაქვს თავდაპირველად შევსებული A და B სვეტების მონაცემები:

1.	დავალაგოთ A და B სვეტი ანბანის მიხედვით;

2.	მოვნიშნოთ A1:A51 დიაპაზონი და შევასრულოთ Data პანელის ბრძანება. ეკრანზე გამოვა Advanced Filter დიალოგი:

3.	Advanced Filter დიალოგში გადამრთველი გადავრთოთ Copy to another location პოზიციაში, ხოლო Copy to ველში შევიტანოთ $D$1;

4.	ჩავრთოთ Unique records only ჩამრთველი და დავაწკაპუნოთ OK ღილაკზე. D სვეტში მივიღებთ კოდების ჩამონათვალს:

ახლა E სვეტში თითოეულ კოდს შეიძლება მიეწეროს შესაბამისი ჯამის გამოსათვლელი ფორმულა. რა თქმა უნდა, კოდის ხუთი მნიშვნელობისათვის ჯამის უშუალო დათვლა პრობლემას არ წარმოადგენს, თუმცა ეს ხერხი მიუღებელია რეალურ სიტუაციაში, როდესაც კოდის რაოდენობა ათასობით შეიძლება განისაზღვროს. ზოგადად ამ ჯამის დათვლა არც თუ ისე ადვილია, რადგან წინასწარ არ არის ცნობილი თითოეული კოდის შესაბამის მონაცემთა რაოდენობა და მისამართი.

ამ მიზნით გამოვიყენოთ მონაცემთა ბლოკთან სამუშაო DSUM(ბლოკი, ველი, კრიტერიუმი) ფუნქცია, რომელიც „ბლოკის“ მონაცემებიდან შეკრებს „ველი“ სვეტის იმ მონაცემებს, რომლებიც „კრიტერიუმს“ აკმაყოფილებენ.

E2 უჯრედში ჩავწეროთ ფორმულა: =DSUM($A$1:$B$51, "რაოდენობა", $D$1:D2)-SUM($E$1:E1). გავავრცელოთ ეს ფორმულა შევსების მარკერით E3:E6 უჯრედებზე.

ამოცანა დასრულებულია.

მონაცემთა შერწყმა

იგივე პრობლემა გადავწყვიტოთ მონაცემთა შერწყმის გამოყენებით. მოცემუ¬ლი გვაქვს თავდაპირველად შევსებული A და B სვეტების მონაცემები.

ამ შემთხვევაში სორტირება აუცილებელი არ არის. დავდგეთ D1 უჯრედზე და შევასრულოთ Data პანელის Consolidate ბრძანება.

Consolidate დიალოგურ ფანჯარაში გავააქტიუროთ Reference ველი და მოვნიშნოთ A1:B51 დიაპაზონი. ეს მისამართი ველში ჩაიწერება.

ჩავრთოთ Top row და Left column ჩამრთველები და დავაწკაპუნოთ OK ღილაკზე. მივიღებთ შემდეგ სურათს:

ამით ამოცანა დასრულებულია.

იგივე პრობლემა გადავწყვიტოთ შუალედური შედეგების გამოთვლის დახმარებით, რომელიც Subtotal დიალოგური ფანჯრიდან ხორციელდება.

მოცემული გვაქვს თავდაპირველად შევსებული A და B სვეტების მონაცემები. ეს მონაცემები ისევ დავალაგოთ A სვეტის მიხედვით.

გავააქტიუროთ მონაცემთა ბლოკის ნებისმიერი უჯრედი და Data პანელის Subtotal ბრძანებით გამოვიძახოთ Subtotal დიალოგური ფანჯარა:

დავაწკაპუნოთ OK ღილაკზე. ყოველი კოდის შემდეგ ჩაემატება ჯამური სტრიქონი. დავაწკაპუნოთ ღილაკზე. ეკრანზე მხოლოდ ჯამები დარჩება:

ყველა მონაცემის გამოსაჩენად საჭიროა ღილაკზე დაწკაპუნება.

საყრდენი ცხრილი

ახლა ვნახოთ, თუ როგორ იქმნება საყრდენი ცხრილი.

გავააქტიუროთ მონაცემთა ბლოკის რომელიმე უჯრედი საწყის ცხრილში და შევასრულოთ Insert პანელის PivotTable ბრძანება. ეკრანზე გამოვა Create PivotTable დიალოგური ფანჯარა.

ზედა გადამრთველში მითითებული იქნება საწყისი ცხრილის დიაპაზონი: Pivot!$A$1:$B$51. ქვედა გადამრთველი გადავრთოთ Existing Worksheet პოზიციაში და Location ველში ჩავწეროთ D5.

OK ღილაკზე დაწკაპუნების შემდეგ გამოჩნდება საყრდენი ცხრილის სქემა, ხოლო მარჯვენა ნაწილში - PivotTable Field List პანელი. ამ პანელში ჩავრთოთ „კოდი“ და „რაოდენობა“ ჩამრთველები, ხოლო „SUM of კოდი“ ღილაკი ∑ Values არიდან გადავიტანოთ Row Labels არეში. საყრდენი ცხრილი ასეთ სახეს მიიღებს:

რიგ-რიგობით გავააქტიუროთ უჯრედები საყრდენი ცხრილის შიგნით და მის გარეთ. დავინახავთ, რომ საყრდენი ცხრილის უჯრედის გააქტიურებისას დავთარში გამოჩნდება PivotTable Field List პანელი, ხოლო ექსელის ინსტრუმენტებში - PivotTable Tools პანელი, რომელსაც ქვემოთ განვიხილავთ.

Insert პანელის PivotTable ბრძანების ნაცვლად რომ PivotTable / PivotChart ბრძანება შეგვესრულებინა, ცხრილთან ერთად მივიღებდით დიაგრამას: